package com.boot.security.server.utils;

import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * excel工具类
 *
 * @author 小威老师
 */
public class ExcelUtil {

    public static void excelLocal(String path, String fileName, String[] headers, List<Object[]> datas) {
        Workbook workbook = getWorkbook(headers, datas);
        if (workbook != null) {
            ByteArrayOutputStream byteArrayOutputStream = null;
            FileOutputStream fileOutputStream = null;
            try {
                byteArrayOutputStream = new ByteArrayOutputStream();
                workbook.write(byteArrayOutputStream);

                String suffix = ".xls";
                File file = new File(path + File.separator + fileName + suffix);
                if (!file.getParentFile().exists()) {
                    file.getParentFile().mkdirs();
                }

                fileOutputStream = new FileOutputStream(file);
                fileOutputStream.write(byteArrayOutputStream.toByteArray());
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (fileOutputStream != null) {
                        fileOutputStream.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
                try {
                    if (byteArrayOutputStream != null) {
                        byteArrayOutputStream.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }

                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 导出excel
     *
     * @param fileName
     * @param headers
     * @param datas
     * @param response
     */
    public static void excelExport(String fileName, String[] headers, List<Object[]> datas,
                                   HttpServletResponse response) {
        Workbook workbook = getWorkbook(headers, datas);
        if (workbook != null) {
            ByteArrayOutputStream byteArrayOutputStream = null;
            try {
                byteArrayOutputStream = new ByteArrayOutputStream();
                workbook.write(byteArrayOutputStream);

                String suffix = ".xls";
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition",
                        "attachment;filename=" + new String((fileName + suffix).getBytes(), "iso-8859-1"));

                OutputStream outputStream = response.getOutputStream();
                outputStream.write(byteArrayOutputStream.toByteArray());
                outputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (byteArrayOutputStream != null) {
                        byteArrayOutputStream.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }

                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 导出excel到本地
     *
     * @param fileName
     * @param headers
     * @param datas
     */
    public static void excelExport(String fileName, List headers, List<HashMap> datas) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        //新建工作表
        HSSFSheet sheet = workbook.createSheet("sheet1");
        //创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
        HSSFRow row_header = sheet.createRow(0);
        for(int i=0; i<headers.size(); i++){
            //创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
            HSSFCell cell = row_header.createCell(i);
            //设置单元格的值
            cell.setCellValue(headers.get(i).toString());
        }

        for(int j=0; j<datas.size(); j++){
            HSSFRow row = sheet.createRow(j + 1);
            HashMap map = datas.get(j);
            for(int k=0; k<map.size(); k++){
                HSSFCell cell = row.createCell(k);
                //设置单元格的值
                cell.setCellValue(map.get(headers.get(k)).toString());
            }

        }
        try{
            //输出到磁盘中
            FileOutputStream fos = new FileOutputStream(new File(fileName));
            workbook.write(fos);
            workbook.close();
            fos.close();
        }catch (IOException ex){
            ex.printStackTrace();
        }
    }

    /**
     * @param headers 列头
     * @param datas   数据
     * @return
     */
    public static Workbook getWorkbook(String[] headers, List<Object[]> datas) {
        Workbook workbook = new HSSFWorkbook();

        Sheet sheet = workbook.createSheet();
        Row row = null;
        Cell cell = null;
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER_SELECTION);

        Font font = workbook.createFont();

        int line = 0, maxColumn = 0;
        if (headers != null && headers.length > 0) {// 设置列头
            row = sheet.createRow(line++);
            row.setHeightInPoints(23);
            font.setBold(true);
            font.setFontHeightInPoints((short) 13);
            style.setFont(font);

            maxColumn = headers.length;
            for (int i = 0; i < maxColumn; i++) {
                cell = row.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(style);
            }
        }

        if (datas != null && datas.size() > 0) {// 渲染数据
            for (int index = 0, size = datas.size(); index < size; index++) {
                Object[] data = datas.get(index);
                if (data != null && data.length > 0) {
                    row = sheet.createRow(line++);
                    row.setHeightInPoints(20);

                    int length = data.length;
                    if (length > maxColumn) {
                        maxColumn = length;
                    }

                    for (int i = 0; i < length; i++) {
                        cell = row.createCell(i);
                        cell.setCellValue(data[i] == null ? null : data[i].toString());
                    }
                }
            }
        }

        for (int i = 0; i < maxColumn; i++) {
            sheet.autoSizeColumn(i);
        }

        return workbook;
    }

    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    public static List[] readExcel(File file) {
        List[] array = null;
        Workbook wb = null;
        List head = new ArrayList();
        List list = new ArrayList();
        try {
            if (ExcelUtil.isExcel2007(file.getPath())) {
                wb = new XSSFWorkbook(new FileInputStream(file));
            } else {
                wb = new HSSFWorkbook(new FileInputStream(file));
            }
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }

        Sheet sheet = wb.getSheetAt(0);//获取第一张表
        if (sheet != null) {
            if (sheet.getRow(0) != null) {
                array = new ArrayList[2];
                for (int i = 0; i < sheet.getRow(0).getPhysicalNumberOfCells(); i++) {
                    Cell cell = sheet.getRow(0).getCell(i);
                    head.add(cell.getStringCellValue());
                }

                for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                    HashMap map = new HashMap();
                    Row row = sheet.getRow(i);//获取索引为i的行，以0开始
                    for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                        Cell cell = row.getCell(j);
                        DecimalFormat df = new DecimalFormat("#");
                        String val;
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC:// 数字
                                val = df.format(cell.getNumericCellValue());
                                break;
                            default:
                                val = cell.toString();
                                break;
                        }
                        map.put(head.get(j), val);
                    }
                    list.add(map);
                }
                try {
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                array[0] = head;
                array[1] = list;
            }
        }
        return array;
    }

    public static void main(String[] args){
        List[] list = readExcel(new File("d:/aaa.xls"));
        List<HashMap> result = new ArrayList<>();
        for(int i=0; i<list[1].size(); i++){
            HashMap map = (HashMap) list[1].get(i);
            String str = map.get("name").toString();
            for(int j=0; j<str.split("\n").length; j++){
                HashMap m = new HashMap();
                m.put("id", map.get("id").toString());
                m.put("name", str.split("\n")[j].toString());
                result.add(m);
            }
        }
        excelExport("d:/123.xls", list[0], result);
    }
}
